#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import openpyxl
import re

excel_path = r"C:\Users\xiaoyao\Desktop\test.xlsx"
txt_path = r"C:\Users\xiaoyao\Desktop\0514-16new.txt"

workbook = openpyxl.load_workbook(excel_path)
worksheet = workbook["Sheet1"]

l, row, column = [], 2, 1
with open(txt_path, "r", encoding='utf-8') as r:
    for line in r.readlines():
        comma_index = line.find(",[")
        uid = line[:comma_index]
        objects = re.findall('[0-9]+[\s][0-9]+[\s][0-9]+[\s][0-9]+', line)
        for obj in objects:
            obj_list = obj.split(" ")
            if obj_list[2] == "0" and obj_list[3] == "0":
                worksheet.cell(row=row, column=column).value = uid
                worksheet.cell(row=row, column=column + 1).value = obj_list[0]
                worksheet.cell(row=row, column=column + 2).value = obj_list[1]
            else:
                worksheet.cell(row=row, column=column).value = uid
                worksheet.cell(row=row, column=column + 1).value = obj_list[2]
                worksheet.cell(row=row, column=column + 2).value = obj_list[3]
            row += 1
            column = 1
workbook.save(excel_path)
